package cj.hrm.dao;


import cj.hrm.dao.provider.EmployeeDynaSqlProvider;
import cj.hrm.dto.EmployeeDto;
import cj.hrm.entity.Employee;
import cj.hrm.entity.request.EmployeeToExcelRequest;
import org.apache.ibatis.annotations.*;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.mapping.FetchType;

import java.util.List;
import java.util.Map;

import static cj.hrm.util.common.HrmConstants.*;


public interface EmployeeDao {
    //查询
    @Select("select * from " + EMPLOYEETABLE + " ")
    @Results({
            @Result(column = "dept_id", property = "dept",
                    one = @One(select = "cj.hrm.dao.DeptDao.selectById",
                            fetchType = FetchType.EAGER)),
            @Result(column = "sex_id", property = "sex",
                    one = @One(select = "cj.hrm.dao.SexDao.selectById",
                            fetchType = FetchType.EAGER)),
            @Result(column = "education_id", property = "education",
                    one = @One(select = "cj.hrm.dao.EducationDao.selectById",
                            fetchType = FetchType.EAGER)),
            @Result(column = "job_id", property = "job",
                    one = @One(select = "cj.hrm.dao.JobDao.selectById",
                            fetchType = FetchType.EAGER))
            //通过员工表的id去查询user表
            //FetchType.LAZY：懒加载，加载一个实体时，定义懒加载的属性不会马上从数据库中加载
            //FetchType.EAGER：急加载，加载一个实体时，定义急加载的属性会立即从数据库中加载
    })
    List<Employee> get_List();


    @Select("select * from " + EMPLOYEETABLE + "  where name like binary CONCAT('%',#{content},'%')"
            + "or user_id =(select emp_id from " + USERTABLE + " where loginname =#{content})"
            + "or DEPT_ID =(select id from " + DEPTTABLE + " where name like CONCAT('%',#{content},'%'))"
            + "or JOB_ID =(select id from " + JOBTABLE + " where name like CONCAT('%',#{content},'%'))"
            + "or sex_id =(select id from " + SEXTABLE + " where name like CONCAT('%',#{content},'%'))"
            + "or education_id =(select id from " + EDUCATIONTABLE + " where name like CONCAT('%',#{content},'%'))"
            + "or address like binary CONCAT('%',#{content},'%')"
            + "or card_id like binary CONCAT('%',#{content},'%')"
            + "or phone like binary CONCAT('%',#{content},'%')"
            + "or email like binary CONCAT('%',#{content},'%')"
            + "or user_num like binary CONCAT('%',#{content},'%')"
            + "or createdate like binary CONCAT('%',#{content},'%')")
    @Results({
            @Result(column = "user_id", property = "user",
                    one = @One(select = "cj.hrm.dao.UserDao.get_StaticInfoLoginname",
                            fetchType = FetchType.EAGER)),
            @Result(column = "dept_id", property = "dept",
                    one = @One(select = "cj.hrm.dao.DeptDao.selectById",
                            fetchType = FetchType.EAGER)),
            @Result(column = "sex_id", property = "sex",
                    one = @One(select = "cj.hrm.dao.SexDao.selectById",
                            fetchType = FetchType.EAGER)),
            @Result(column = "education_id", property = "education",
                    one = @One(select = "cj.hrm.dao.EducationDao.selectById",
                            fetchType = FetchType.EAGER)),
            @Result(column = "job_id", property = "job",
                    one = @One(select = "cj.hrm.dao.JobDao.selectById",
                            fetchType = FetchType.EAGER))
    })
    List<Employee> get_LikeList(String content);

    @Select("select id from " + EMPLOYEETABLE + " where name = #{employee_name}")
    Integer getIdByName(String employee_name);

    //动态插入员工
    @SelectProvider(type = EmployeeDynaSqlProvider.class, method = "insertEmployee")
    void insert_Employee(Employee employee);

    //动态修改员工
    @SelectProvider(type = EmployeeDynaSqlProvider.class, method = "updateEmployee")
    void update_Employee(Employee employee);
    //根据参数查询员工总数
    @SelectProvider(type = EmployeeDynaSqlProvider.class, method = "count")
    public Integer count(Map<String, Object> params);

    @Select("select * from " + EMPLOYEETABLE + " where id = #{id}")
    @Results({
            @Result(column = "dept_id", property = "dept",
                    one = @One(select = "cj.hrm.dao.DeptDao.selectById",
                            fetchType = FetchType.EAGER)),
            @Result(column = "sex_id", property = "sex",
                    one = @One(select = "cj.hrm.dao.SexDao.selectById",
                            fetchType = FetchType.EAGER)),
            @Result(column = "education_id", property = "education",
                    one = @One(select = "cj.hrm.dao.EducationDao.selectById",
                            fetchType = FetchType.EAGER)),
            @Result(column = "job_id", property = "job",
                    one = @One(select = "cj.hrm.dao.JobDao.selectById",
                            fetchType = FetchType.EAGER))
    })
    Employee get_Info(Integer id);

    // 根据id删除员工
    @Delete(" delete from " + EMPLOYEETABLE + " where id = #{id} ")
    void deleteEmployeeById(Integer id);

   //根据参数动态查询员工
    @SelectProvider(type = EmployeeDynaSqlProvider.class, method = "selectWhitParam")
    @Results({
            @Result(column = "user_id", property = "user",
                    one = @One(select = "cj.hrm.dao.UserDao.get_StaticInfoLoginname",
                            fetchType = FetchType.EAGER)),
            @Result(column = "dept_id", property = "dept",
                    one = @One(select = "cj.hrm.dao.DeptDao.selectById",
                            fetchType = FetchType.EAGER)),
            @Result(column = "sex_id", property = "sex",
                    one = @One(select = "cj.hrm.dao.SexDao.selectById",
                            fetchType = FetchType.EAGER)),
            @Result(column = "education_id", property = "education",
                    one = @One(select = "cj.hrm.dao.EducationDao.selectById",
                            fetchType = FetchType.EAGER)),
            @Result(column = "job_id", property = "job",
                    one = @One(select = "cj.hrm.dao.JobDao.selectById",
                            fetchType = FetchType.EAGER))
    })
    List<Employee> selectByPage(Map<String, Object> params);

    //根据参数动态查询员工
    @Select("select " +
            "employee_inf.name as employeeName " +
            ",employee_inf.user_num as userNum " +
            ",user_inf.loginname as loginName " +
            ",sex_inf.name as sex " +
            ",employee_inf.phone as phone " +
            ",user_inf.email as email " +
            ",job_inf.name as jobName " +
            ",dept_inf.name as deptName " +
            ",education_inf.name as educationName " +
            ",employee_inf.card_id as cardId " +
            ",employee_inf.address as address " +
            ",employee_inf.createdate as creatTimeStr " +
            "from employee_inf " +
            "left join user_inf on user_inf.emp_id = employee_inf.user_id " +
            "left join dept_inf on dept_inf.id = employee_inf.dept_id " +
            "left join sex_inf on sex_inf.id = employee_inf.sex_id " +
            "left join education_inf on education_inf.id = employee_inf.education_id " +
            "left join job_inf on job_inf.id = employee_inf.job_id")
    List<EmployeeDto> findEmployeeDtoList();

    @Select("select count(*) from " + EMPLOYEETABLE + "  where name like binary CONCAT('%',#{content},'%')"
            + "or user_id =(select emp_id from " + USERTABLE + " where loginname =#{content})"
            + "or dept_id =(select id from " + DEPTTABLE + " where name like CONCAT('%',#{content},'%'))"
            + "or job_id =(select id from " + JOBTABLE + " where name like CONCAT('%',#{content},'%'))"
            + "or sex_id =(select id from " + SEXTABLE + " where name like CONCAT('%',#{content},'%'))"
            + "or education_id =(select id from " + EDUCATIONTABLE + " where name like CONCAT('%',#{content},'%'))"
            + "or address like binary CONCAT('%',#{content},'%')"
            + "or card_id like binary CONCAT('%',#{content},'%')"
            + "or phone like binary CONCAT('%',#{content},'%')"
            + "or email like binary CONCAT('%',#{content},'%')"
            + "or user_num like binary CONCAT('%',#{content},'%')"
            + "or createdate like binary CONCAT('%',#{content},'%')")
    public Integer countEmployee(String content);


   /* @Select("select con_id from " + EMPLOYEETABLE + " order by id DESC limit 1 ")
    Integer togetCon_id();

    @Select("select sal_id from " + EMPLOYEETABLE + " order by id DESC limit 1 ")
    Integer togetSal_id();*/

    @Select("select che_id from " + EMPLOYEETABLE + " order by id DESC limit 1 ")
    Integer togetChe_id();

    //让结果只显示省份即可
    @Select("select SUBSTRING(address,1,2) from " + EMPLOYEETABLE + " ")
    List<String> get_addressList();

    int batchExcelInsert(@Param("list") List<EmployeeToExcelRequest> list);
}
